package amu.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import amu.model.Book;
import amu.model.Customer;
import amu.model.Review;

public class ReviewDAO {
	Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    
    public List<Review> browse(Book book) {
        List<Review> reviews = new ArrayList<Review>();

        try {
            connection = Database.getConnection();
            String query = "SELECT * FROM review WHERE book_id=?";
            statement = connection.prepareStatement(query);

            statement.setInt(1, book.getId());
            
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
            	CustomerDAO customerDAO = new CustomerDAO();
            	Customer customer = customerDAO.findByID(resultSet.getInt("customer_id"));
                                            	
                reviews.add(new Review(resultSet.getInt("id"), customer, resultSet.getString("review"), book, resultSet.getInt("rating"), resultSet.getInt("num_helpful"), resultSet.getInt("num_unhelpful")));
            }
        } catch (SQLException exception) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
        } finally {
            Database.close(connection, statement, resultSet);
        }

        return reviews;
    }

    public Review read(int id) {
        Review review = null;

        try {
            connection = Database.getConnection();

            String query = "SELECT * FROM review WHERE id=?";
            statement = connection.prepareStatement(query);
            statement.setInt(1, id);

            resultSet = statement.executeQuery();

            if (resultSet.next()) {
            	CustomerDAO customerDAO = new CustomerDAO();
            	Customer customer = customerDAO.findByID(resultSet.getInt("customer_id"));
                
            	BookDAO bookDAO = new BookDAO();
            	Book book = bookDAO.findByID(resultSet.getInt("book_id"));
            	
                review = new Review(id, customer, resultSet.getString("review"),book, resultSet.getInt("rating"), resultSet.getInt("num_helpful"), resultSet.getInt("num_unhelpful")); 
            } 
        } catch (SQLException exception) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
        } finally {
            Database.close(connection, statement, resultSet);
        }

        return review;
    }

	public boolean add(Review review) {

        try {
            connection = Database.getConnection();

            String query = "INSERT INTO review (customer_id, review, book_id, num_helpful, num_unhelpful, rating) VALUES (?,?,?,?,?,?)";
            statement = connection.prepareStatement(query);
            statement.setInt(1, review.getCustomer().getId());
            statement.setString(2, review.getReview());
            statement.setInt(3, review.getBook().getId());
            statement.setInt(4, review.getNumHelpful());
            statement.setInt(5, review.getNumUnhelpful());
            statement.setInt(6, review.getRating());

            if (statement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException exception) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
        } finally {
            Database.close(connection, statement, resultSet);
        }

        return false;
		
	}
	
	public boolean update(Review review) {

        try {
            connection = Database.getConnection();

            String query = "UPDATE review SET num_helpful=?, num_unhelpful=? WHERE id=?";
            statement = connection.prepareStatement(query);
            statement.setInt(1, review.getNumHelpful());
            statement.setInt(2, review.getNumUnhelpful());
            statement.setInt(3, review.getId());

            if (statement.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException exception) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
        } finally {
            Database.close(connection, statement, resultSet);
        }

        return false;
		
	}
}
